﻿using System;
using System.Collections.Generic;
using System.Data.SQLite;
using System.Linq;
using System.Text;
using Zimmerreservierung.Model;

namespace Zimmerreservierung.Controller
{
    public class KundenController
    {
        private static KundenController instance;
        private SQLiteConnection connection;
        private int lastInsertedId;

        public KundenController()
        {
            connection = new SQLiteConnection(Properties.Settings.Default.dbConnectionString);
        }

        public static KundenController getInstance()
        {
            if (instance == null)
                instance = new KundenController();

            return instance;
        }

        public List<Kunde> GetKunden()
        {
            List<Kunde> kunden = new List<Kunde>();
            const string sql = "SELECT * FROM Kunde ORDER BY Name";

            try
            {
                connection.Open();
                using (SQLiteCommand cmd = new SQLiteCommand(connection))
                {
                    cmd.CommandText = sql;

                    SQLiteDataReader reader = cmd.ExecuteReader();
                    while (reader.Read())
                    {
                        Kunde k = new Kunde
                        {
                            Id = Convert.ToInt32(reader["ID"]),
                            Name = Convert.ToString(reader["Name"]),
                            Adresse = Convert.ToString(reader["Rechnungsaddresse"]),
                            Firmenname = Convert.ToString(reader["Firmenname"]),
                            TelNr = Convert.ToString(reader["Telefonnummer"]),
                            FaxNr = Convert.ToString(reader["Faxnummer"]),
                            Mail = Convert.ToString(reader["Email"]),
                            WebAdress = Convert.ToString(reader["Web"]),
                            Notiz = Convert.ToString(reader["Notiz"]),
                            Rabat = Convert.ToInt32(Convert.ToDouble(reader["rabatt"]) * 100)
                        };

                        kunden.Add(k);
                    }
                }
            }
            catch
            {
                throw;
            }
            finally
            {
                connection.Close();
            }

            return kunden;
        }

        public Kunde GetKunde(int id)
        {
            Kunde kunde = null;
            string sql = "SELECT * FROM Kunde where ID = " + id + " ORDER BY Name";

            try
            {
                connection.Open();
                using (SQLiteCommand cmd = new SQLiteCommand(connection))
                {
                    cmd.CommandText = sql;

                    SQLiteDataReader reader = cmd.ExecuteReader();
                    while (reader.Read())
                    {
                        kunde = new Kunde
                        {
                            Id = Convert.ToInt32(reader["ID"]),
                            Name = Convert.ToString(reader["Name"]),
                            Adresse = Convert.ToString(reader["Rechnungsaddresse"]),
                            Firmenname = Convert.ToString(reader["Firmenname"]),
                            TelNr = Convert.ToString(reader["Telefonnummer"]),
                            FaxNr = Convert.ToString(reader["Faxnummer"]),
                            Mail = Convert.ToString(reader["Email"]),
                            WebAdress = Convert.ToString(reader["Web"]),
                            Notiz = Convert.ToString(reader["Notiz"]),
                            Rabat = Convert.ToInt32(Convert.ToDouble(reader["rabatt"]) * 100)
                        };
                    }
                }

            }
            catch
            {
                throw;
            }
            finally
            {
                connection.Close();
            }

            return kunde;
        }

        public bool StoreKunde(string name, string rAddr, string fName, string strRabatt, string telNr, string faxNr, string eMail, string web, string notiz)
        {
            int actionOK = 0;

            try
            {
                connection.Open();
                using (SQLiteCommand command = new SQLiteCommand(connection))
                {
                    command.CommandText = "INSERT INTO Kunde ( ID, Name, Rechnungsaddresse, Firmenname, Rabatt, Telefonnummer, Faxnummer, Email, Web, Notiz ) "
                                                   + "VALUES (NULL, '" + name + "', '" + rAddr + "', '" + fName + "', '" + strRabatt + "', "
                                                              + "'" + telNr + "', '" + faxNr + "', '" + eMail + "', '" + web + "','" + notiz + "' );";
                    actionOK = command.ExecuteNonQuery();

                    command.CommandText = "SELECT last_insert_rowid()";
                    lastInsertedId = Convert.ToInt32(command.ExecuteScalar());
                }

            }
            catch
            {
                throw;
            }
            finally
            {
                connection.Close();
            }

            return actionOK == 1 ? true : false;
        }

        public bool UpdateKunde(int id, string name, string rAddr, string fName, string strRabatt, string telNr, string faxNr, string eMail, string web, string notiz)
        {
            int actionOK = 0;

            try
            {
                connection.Open();
                using (SQLiteCommand command = new SQLiteCommand(connection))
                {
                    command.CommandText = "UPDATE Kunde SET Name='" + name + "', Rechnungsaddresse = '" + rAddr + "', Firmenname='" + fName + "', Notiz='" + notiz + "',"
                                                        + " Rabatt='" + strRabatt + "', Telefonnummer='" + telNr + "', Email='" + eMail + "',"
                                                        + " Web='" + web + "', Faxnummer='" + faxNr + "' "
                                                        + "WHERE ID = " + id + ";";
                    actionOK = command.ExecuteNonQuery();
                }
            }
            catch
            {
                throw;
            }
            finally
            {
                connection.Close();
            }

            return actionOK == 1 ? true : false;
        }

        public bool RemoveKunde(int id)
        {
            int actionOK = 0;

            try
            {
                connection.Open();
                using (SQLiteCommand command = new SQLiteCommand(connection))
                {
                    command.CommandText = "DELETE FROM Kunde WHERE ID = " + id;
                    actionOK = command.ExecuteNonQuery();
                }
            }
            catch
            {
                throw;
            }
            finally
            {
                connection.Close();
            }

            return actionOK == 1 ? true : false;
        }

        public int GetLastInsertedKundenId()
        {
            return lastInsertedId;
        }

        public void startConnection(String connectionString)
        {
            connection.Close();
            connection = new SQLiteConnection(connectionString);
        }
    }
}
